Generated code - Sorting, SelfServicing

Preface

This section discusses briefly the serverside sorting capabilities of LLBLGen Pro. The sorting discussed below is executed as an ORDER BY clause in the generated query. If you want to sort the data in an already fetched collection class, you should use the Sort() method available on an entity collection. Please consult the LLBLGen Pro reference manual for details for Sort().

Upgrading from v1.0.200x.y: no SortClauseFactory

In previous versions of LLBLGen Pro, v1.0.2005.1 and earlier, by default a class called SortClauseFactory was generated. This class contained for each field in each entity a convenient method to produce a SortClause instance. In larger projects however this lead to a very big class which was unusable in VS.NET due to the high number of overloads of a single method. In v2.0 of LLBLGen Pro this class is no longer generated by default and is discouraged to be used in your code. You can still generate this class however, simply enable to SortClauseFactory generation task in the run queue of your preset of choice (See: Designer - Generating code).

This documentation will avoid the usage of the SortClauseFactory class, unless stated otherwise. If you need information about the SortClauseFactory class, please consult the documentation of v1.0.2005.1, still available at our website.

Sorting

Sorting is the ability to order data in one or more fields ascending (A -> Z) or descending (Z -< A). You do this by constructing a SortExpression with one or more SortClauses. SortClauses are simple definitions which contain information about which field to sort and in which direction (ascending/descending). In the previous section, Advanced Filtering, aliassing entities has been introduced, and you can refer to a specific field in a specific aliased entity by specifying the right alias with the SortClause constructor call. You can also use the EntityField's SetObjectAlias method.

When you're using the native language filter construction method to formulate filters, it's convenient to also use this for SortExpressions and sortclauses. Below is an example which creates a SortExpression to sort on Customer.Country Ascending and Customer.CompanyName descending. Both methods are shown (regular and native language).

Note:

If you specify a sort clause or a set of sortclauses and a RelationCollection (which is almost always the case with a typed list) while you also specify that duplicates are not allowed, be sure the sort clauses are referring to fields in the resultset, otherwise the database can't obey the sort rule and will throw an exception, since all fields mentioned in an ORDER BY clause (which is the result of a sort clause) have to be in the resultset when a DISTINCT statement (the result of the specification that no duplicate rows have to be retrieved) is included. When you want to sort on a field which has an aggregate function or an expression applied to it, be sure to specify the aggregate function or expression object to the field in the SortClause as well, with the same alias.

Case-insensitive sorting

On case-sensitive databases (default Oracle installations, Firebird etc.) it can be you want to sort alpha-numeric data case-insensitive. To achieve that, you should set the SortClause object's property CaseSensitiveCollation to true, identical to the FieldLikePredicate system for case-insensitive filtering. Setting this property to true will make the query generator emit UPPER() around the field, thus UPPER(fieldname), or equivalent function for UPPER() on the particular database. Example, which sorts case insensitive on companyname: